package cn.vastbase.jdbc.base;

import org.junit.Test;

import java.sql.*;

/**
 * <p>基础操作示例：包括增、删、改、查、建表，普通SQL、预编译SQL </p>
 * <p>适用于Vastbase G100 2.2 Build 15.4 </p>
 */
public class CrudTestCase {

    /* 定义连接对象 */
    static Connection conn = null;
    static String cname = "org.postgresql.Driver";
    static String url = "jdbc:postgresql://172.16.55.175:6666/zxsdb?loggerLevel=OFF";
    static String username = "zxs";
    static String passwd = "Vastdata#0730";
    /* 定义 SQL 语句执行对象 */
    static Statement stmt = null;
    static PreparedStatement pstmt = null;
    /* 定义结果集对象 */
    static ResultSet rs = null;

    public Connection conn() {
        try {
            Class.forName(cname);
            return conn = DriverManager.getConnection(url, username, passwd);
        } catch (ClassNotFoundException | SQLException e) {
            System.out.println("[FAIL] conn database fail." + e.getMessage());
        }
        return null;

    }

    public void close() {
        try {
            /* 关闭结果集对象 */
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close resultSet fail." + e.getMessage());
        }
        try {
            /* 关闭语句对象 */
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            /* 关闭语句对象 */
            if (pstmt != null)
                pstmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            /* 关闭连接 */
            conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close connection fail." + e.getMessage());
        }
    }

    @Test
    public void create() {
        try {
            /* 初始化连接 */
            if (conn() == null)
                return;

            /* 创建语句对象 */
            stmt = conn.createStatement();

            int rc = stmt.executeUpdate("DROP TABLE IF EXISTS t_fruit;" +
                    "CREATE TABLE t_fruit(fruit_id INTEGER, fruit_name VARCHAR(32))");

            if (rc != 0) {
                System.out.println("[FAIL] fail to create table.\n");
            } else {
                System.out.println("[SUCCESS] create table successfully.\n");
            }
            /* 关闭语句对象 */
            stmt.close();
            /* 关闭连接 */
            conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] execute sql fail." + e.getMessage());
        } finally {
            close();
        }
    }

    @Test
    public void insert() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("INSERT INTO t_fruit VALUES (?, ?)");
            pstmt.setInt(1, 1);
            pstmt.setString(2, "apple");
            pstmt.addBatch();

            pstmt.setInt(1, 2);
            pstmt.setString(2, "orange");
            pstmt.addBatch();

            pstmt.setInt(1, 3);
            pstmt.setString(2, "banana");
            pstmt.addBatch();
            pstmt.executeBatch();
            System.out.println("[SUCCESS] insert successfully.");
        } catch (SQLException e) {
            System.out.println("[FAIL] insert fail." + e.getMessage());
        } finally {
            close();
        }
    }


    @Test
    public void select() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("select fruit_id, fruit_name from t_fruit");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println("t_fruit[fruit_id=" + rs.getInt(1) + ",fruit_name=" + rs.getString(2) + "]");
            }
        } catch (SQLException e) {
            System.out.println("[FAIL] select fail." + e.getMessage());
        } finally {
            close();
        }
    }

    @Test
    public void update() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("update t_fruit set fruit_name=? where fruit_id=?");
            pstmt.setString(1, "banana");
            pstmt.setInt(2, 1);
            pstmt.executeUpdate();
            System.out.println("[SUCCESS] update successfully.\n");
        } catch (SQLException e) {
            System.out.println("[FAIL] update fail." + e.getMessage());
        } finally {
            close();
        }
    }

    @Test
    public void delete() {
        try {
            if (conn() == null)
                return;
            pstmt = conn.prepareStatement("delete from t_fruit where fruit_id=?");
            pstmt.setInt(1, 1);
            pstmt.executeUpdate();
            System.out.println("[SUCCESS] delete successfully.");
        } catch (SQLException e) {
            System.out.println("[FAIL] delete fail." + e.getMessage());
        } finally {
            close();
        }
    }


}
